# ======= Setup =======
suppressPackageStartupMessages({
  library(dplyr)
  library(tidyr)
  library(purrr)
  library(stringr)
})

options(stringsAsFactors = FALSE)

# --- Paths --------------------------------------------------------------------
if (!requireNamespace("here", quietly = TRUE)) install.packages("here", quiet = TRUE)
base_dir <- Sys.getenv("PROJECT_DIR", unset = normalizePath(here::here(), winslash = "/"))

raw_path <- file.path(base_dir, "Data", "Economic", "Spatial_Tax",
                      "hex7_FTE_WageBand_Industry5d_Monthly.csv")
out_dir  <- file.path(base_dir, "Processed")
dir.create(out_dir, showWarnings = FALSE, recursive = TRUE)

df <- read.csv(raw_path)

# Clean + derive month pieces
df <- df %>%
  rename(fte = FTE) %>%
  mutate(
    fte   = ifelse(fte == "<10", 0, as.numeric(fte)),
    year  = as.integer(substr(month, 1, 4)),
    month = as.integer(substr(month, 6, 7))
  ) %>%
  filter(is.finite(fte), fte > 0) %>%            # drop zeros & non-numeric
  mutate(monthly = year * 12 + month)

# Wage band → category
map <- c(
  "[0.0, 400.0)"=1,"[400.0, 800.0)"=2,"[800.0, 1600.0)"=3,"[1600.0, 3200.0)"=4,
  "[3200.0, 6400.0)"=5,"[6400.0, 12800.0)"=6,"[12800.0, 25600.0)"=7,"[25600.0, 51200.0)"=8,
  "[51200.0, 102400.0)"=9,"[102400.0, 204800.0)"=10,"[204800.0, 409600.0)"=11,"[409600.0, 819200.0)"=12,
  "[819200.0, 1638400.0)"=13,"[1638400.0, 10000000.0)"=14
)
df$wage_band_category <- unname(map[df$RealWageBand])

# Category midpoint (tiering)
mid <- c(200,600,1200,2400,4800,9600,19200,38400,76800,153600,307200,614400,1228800, 2000000.0)

df <- df %>%
  mutate(
    real_wage = mid[wage_band_category],
    wage_tier = case_when(
      !is.na(real_wage) & real_wage <= 6400   ~ 1L,   # low (bands 1–5)
      !is.na(real_wage) & real_wage >= 38400  ~ 3L,   # high
      !is.na(real_wage)                       ~ 2L,   # middle
      TRUE                                    ~ NA_integer_
    )
  )

# Collapse to monthly sums by tier
wide <- df %>%
  group_by(hex7, year, month, wage_tier) %>%
  summarise(fte = sum(fte), .groups = "drop") %>%
  mutate(var = paste0("fte_wage_", wage_tier)) %>%
  select(-wage_tier) %>%
  tidyr::pivot_wider(names_from = var, values_from = fte)

# Make sure all three tier columns exist; keep strict NA propagation
for (v in c("fte_wage_1", "fte_wage_2", "fte_wage_3")) {
  if (!v %in% names(wide)) wide[[v]] <- NA_real_
}

wide <- wide %>%
  mutate(
    monthly    = year * 12 + month,
    fte_wage_0 = fte_wage_1 + fte_wage_2 + fte_wage_3
  ) %>%
  select(hex7, year, month, monthly, fte_wage_0, fte_wage_1, fte_wage_2, fte_wage_3)

saveRDS(wide, file.path(out_dir, "hex7_wage_tier_level_monthly.rds"))
write.csv(wide, file.path(out_dir, "hex7_wage_tier_level_monthly.csv"), row.names = FALSE)

# ======= 2) Annual levels (Stata-style) =======
wide <- wide %>%
  mutate(hex_7_id = as.integer(factor(hex7, levels = sort(unique(hex7)))))

annual_long <- wide %>%
  group_by(hex7, hex_7_id, year) %>%
  summarise(
    fte_wage_0 = if (all(is.na(fte_wage_0))) NA_real_ else mean(fte_wage_0, na.rm = TRUE),
    fte_wage_1 = if (all(is.na(fte_wage_1))) NA_real_ else mean(fte_wage_1, na.rm = TRUE),
    fte_wage_2 = if (all(is.na(fte_wage_2))) NA_real_ else mean(fte_wage_2, na.rm = TRUE),
    fte_wage_3 = if (all(is.na(fte_wage_3))) NA_real_ else mean(fte_wage_3, na.rm = TRUE),
    .groups = "drop"
  ) %>%
  mutate(fte = fte_wage_0)

saveRDS(annual_long, file.path(out_dir, "hex7_wage_tier_level_annual_long.rds"))
write.csv(annual_long, file.path(out_dir, "hex7_wage_tier_level_annual_long.csv"), row.names = FALSE)

annual_wide <- annual_long %>%
  pivot_longer(cols = c(fte_wage_0, fte_wage_1, fte_wage_2, fte_wage_3, fte),
               names_to = "var", values_to = "value") %>%
  mutate(var_year = paste0(var, "_", year)) %>%
  select(hex7, hex_7_id, var_year, value) %>%
  pivot_wider(names_from = var_year, values_from = value)

saveRDS(annual_wide, file.path(out_dir, "hex7_wage_tier_level_annual.rds"))
write.csv(annual_wide, file.path(out_dir, "hex7_wage_tier_level_annual.csv"), row.names = FALSE)

 